using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Xsl;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Services.Localization;

namespace Code5Systems.FormMaster.Business
{
	
	public partial class FormDataController
	{
		//Implements IPortable
		
#region Database Related Methods
		
		public FormDataInfo Get(int formDataID, int moduleId)
		{
			return CBO.FillObject<FormDataInfo>(Code5Systems.FormMaster.Data.DataProvider.Instance().GetFormData(formDataID, moduleId));
		}
		
		public string GetFormResults(int moduleId, DateTime dtStart, DateTime dtEnd, int pageNum, int pageSize, bool fromControl = true)
		{
			bool bHaveData = false;
			SqlDataReader dr = default(SqlDataReader);
			System.Text.StringBuilder sbXml = new System.Text.StringBuilder(50000);
			dr = (SqlDataReader) (Code5Systems.FormMaster.Data.DataProvider.Instance().ListFormData(moduleId, dtStart, dtEnd, pageNum, pageSize));
			
			while (dr.Read())
			{
				sbXml.Append(dr.GetValue(0));
			}
			
			if (sbXml.Length > 0)
			{
				bHaveData = true;
			}
			else
			{
				throw (new ArgumentException("NoDataAvailable"));
			}
			
			if (bHaveData)
			{
				dr.NextResult();
				if (dr.Read())
				{
					_intReportRecCount = dr.GetInt32(0);
				}
			}
			
			dr.Close();
			
			return sbXml.ToString();
			
		}
		
		public int GetFormDataToCopyCount(int moduleId, string idCsv)
		{
			return Code5Systems.FormMaster.Data.DataProvider.Instance().GetFormDataToCopyCount(moduleId, idCsv);
		}
		
		public List<FormDataInfo> GetFormDataToCopy(int moduleId, string idCsv)
		{
			return CBO.FillCollection<FormDataInfo>(Code5Systems.FormMaster.Data.DataProvider.Instance().GetFormDataToCopy(moduleId, idCsv));
		}
		
		public int GetExttableDataToCopyCount(FormInfo oFormInfo)
		{
			string sql = default(string);
			sql = "SELECT COUNT(*) FROM {0}";
			sql = string.Format(sql, oFormInfo.CurrentPage.ExtTableName);
			DataTable dt = Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteSQL(oFormInfo.ExtTableConnStr, sql);
			int extCount = int.Parse((string) (dt.Rows[0][0].ToString()));
			
			sql = "SELECT COUNT(*) FROM {0}C5SLLC_FormData WHERE FormID = {1}";
			sql = string.Format(sql, Code5Systems.FormMaster.Data.DataProvider.Instance().ObjectQualifier, oFormInfo.FormID);
			dt = Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteSQL(oFormInfo.ConnectionString, sql);
			int intCount = int.Parse((string) (dt.Rows[0][0].ToString()));
			
			return extCount - intCount;
		}
		
		public string GetExtTableSelectAll(string moduleID, bool addSelectNull = true)
		{
			string temp = default(string);
			List<string> tables = new List<string>();
			List<string> columms = new List<string>();
			List<string> joins = new List<string>();
			int pageNum = 2;
			DataTable fieldTable = default(DataTable);
			DataRowView[] keyFieldRow;
			StringBuilder sbQuery = new StringBuilder();
			FormController formCtrl = new FormController(int.Parse(moduleID));
			const string selCols = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = \'{0}\'";
			
			if (addSelectNull)
			{
				sbQuery.Append("SELECT Null As FormDataID" + Environment.NewLine + ",");
			}
			else
			{
				sbQuery.Append("SELECT" + Environment.NewLine);
			}
			
			// Get the Tables that reference the table mapped to page one
			DataSet dsTableInfo = formCtrl.ExecuteDataSet((string) (formCtrl.Pages[0].ExtTableConnStr), "EXEC sp_help [" + formCtrl.Pages[0].ExtTableName + "]");
			
			// If First Table is referenced by foreign key, sp_help will return 8 tables otherwise 7
			// The 8th table will have the Table names which reference the first table.
			if (dsTableInfo.Tables.Count == 8)
			{
				fieldTable = dsTableInfo.Tables[7];
				foreach (DataRow row in fieldTable.Rows)
				{
					temp = (row[0]).ToString();
					temp = (string) (temp.Split(':')[0]);
					tables.Add(temp);
				}
			}
			
			columms.Add("Ext1.*" + Environment.NewLine);
			foreach (string extTable in tables)
			{
				dsTableInfo = formCtrl.ExecuteDataSet((string) (formCtrl.Pages[0].ExtTableConnStr), "EXEC sp_help [" + extTable + "]");
				fieldTable = dsTableInfo.Tables[1];
				fieldTable.DefaultView.Sort = "Column_name";
				keyFieldRow = fieldTable.DefaultView.FindRows(formCtrl.Pages[0].ExtIdentityColumn);
				if (keyFieldRow.Count() == 1)
				{
					temp = (string) (extTable.Split('.')[2]);
					using (SqlDataReader dr = (SqlDataReader) (Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteDataReader("", string.Format(selCols, temp))))
					{
						while (dr.Read())
						{
							if (dr.GetString(0) != formCtrl.Pages[0].ExtIdentityColumn)
							{
								columms.Add(string.Format(",Ext{0}.[{1}]{2}", pageNum, dr.GetString(0), Environment.NewLine));
							}
						}
					}
					
					
					
					//columms.Add(String.Format(",Ext{0}.*", pageNum))
					joins.Add(string.Format("JOIN {0} AS Ext{1} ON Ext1.[{2}] = Ext{1}.[{2}]{3}", extTable, pageNum, formCtrl.Pages[0].ExtIdentityColumn, Environment.NewLine));
					pageNum++;
				}
			}
			
			foreach (string col in columms)
			{
				sbQuery.Append(col);
			}
			
			sbQuery.Append(string.Format("FROM [{0}] AS Ext1{1}", formCtrl.Pages[0].ExtTableName, Environment.NewLine));
			
			foreach (string join in joins)
			{
				sbQuery.Append(join);
			}
			
			
			return sbQuery.ToString();
			
		}
		
		public DataSet GetFormResultsExt(FormInfo formInfo, int moduleId, DateTime dtStart, DateTime dtEnd, int pageNum, int pageSize, bool fromControl = true)
		{
			bool haveData = false;
			DataSet ds = null;
			
			Dictionary<int, int> objDict = new Dictionary<int, int>();
			
			System.Text.StringBuilder sbQuery = new System.Text.StringBuilder();
			
			//We will need to update the FormDataID from in the Resultset later so Select Null as FormDataID
			using (SqlDataReader dr = (SqlDataReader) (Code5Systems.FormMaster.Data.DataProvider.Instance().ListFormDataExt(moduleId, dtStart, dtEnd, pageNum, pageSize)))
			{
				//sbQuery.Append("SELECT Null As FormDataID, Ext.* FROM [" & formInfo.CurrentPage.ExtTableName & "] AS Ext" & Environment.NewLine)
				sbQuery.Append(GetExtTableSelectAll((formInfo.ModuleID).ToString()));
				sbQuery.Append(string.Format("WHERE Ext1.[{0}] IN (", formInfo.CurrentPage.ExtIdentityColumn));
				
				while (dr.Read())
				{
					if (!objDict.ContainsKey(dr.GetInt32(1)))
					{
						objDict.Add(dr.GetInt32(1), dr.GetInt32(0));
						sbQuery.Append((dr.GetValue(1)).ToString() + ",");
					}
					haveData = true;
				}
				
				if (!haveData)
				{
					sbQuery.Append("0,");
				}
				
				sbQuery.Replace(",", "", sbQuery.Length - 1, 1);
				sbQuery.Append(")");
				
				if (haveData)
				{
					dr.NextResult();
					if (dr.Read())
					{
						_intReportRecCount = dr.GetInt32(0);
					}
				}
			}
			
			
			ds = Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteDataSet(formInfo.ExtTableConnStr, sbQuery.ToString());
			
			//Replace Null with the FormDataID associated with the ExtTableRowId
			foreach (DataRow row in ds.Tables[0].Rows)
			{
				if (objDict.ContainsKey(System.Convert.ToInt32(row[1])))
				{
					row[0] = objDict[System.Convert.ToInt32(row[1])];
				}
			}
			
			//Add the recordCount Table to the DataSet
			DataTable oDataTable = new DataTable("Records");
			DataColumn oDataColumn = new DataColumn("RecordCount");
			oDataTable.Columns.Add(oDataColumn);
			
			DataRow oDataRow = oDataTable.NewRow();
			oDataRow["RecordCount"] = _intReportRecCount;
			oDataTable.Rows.Add(oDataRow);
			ds.Tables.Add(oDataTable);
			
			return ds;
			
		}
		
		public DataSet GetExternalData(FormInfo formInfo, FormDataInfo formDataInfo)
		{
			System.Text.StringBuilder sbQuery = new System.Text.StringBuilder();
			FormController objFormCtlr = new FormController(formInfo.ModuleID);
			sbQuery.Append(GetExtTableSelectAll((formInfo.ModuleID).ToString(), false));
			sbQuery.Append(string.Format("WHERE Ext1.[{0}] = {1}", objFormCtlr.Pages[0].ExtIdentityColumn, formDataInfo.ExtTableRowId));
			
			//Dim strQuery As String = "SELECT * FROM [" & formInfo.ExtTableName & "]" & Environment.NewLine
			//strQuery &= "WHERE " & formInfo.ExtIdentityColumn & " = " & formDataInfo.ExtTableRowId
			
			return Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteDataSet(formInfo.ExtTableConnStr, sbQuery.ToString());
			
		}
		
		public DataSet GetDataSetFromFormResults(string xml, string modulePath, bool fromControl = true)
		{
			XslCompiledTransform oTransDoc = default(XslCompiledTransform);
			XsltArgumentList oXsltArg = default(XsltArgumentList);
			StringWriter strStream = default(StringWriter);
			NameTable nt = new NameTable();
			
			XmlNamespaceManager nsmgr = new XmlNamespaceManager(nt);
			nsmgr.AddNamespace("", "");
			
			bool bHaveData = true;
			
			//Create the XmlParserContext.
			XmlParserContext context = new XmlParserContext(null, nsmgr, null, XmlSpace.None);
			
			oTransDoc = new XslCompiledTransform();
			
			oXsltArg = new XsltArgumentList();
			
			if (fromControl)
			{
				oTransDoc.Load(HttpContext.Current.Server.MapPath(modulePath + "assets/xslt/FormResultsDataSet.xslt"));
			}
			else
			{
				oTransDoc.Load(HttpContext.Current.Server.MapPath("assets/xslt/FormResultsDataSet.xslt"));
			}
			
			strStream = new StringWriter();
			
			oTransDoc.Transform(new XmlTextReader(xml, XmlNodeType.Document, context), oXsltArg, XmlWriter.Create(strStream), null);
			
			XmlTextReader reader = new XmlTextReader(strStream.ToString(), XmlNodeType.Document, context);
			
			strStream.Close();
			
			DataSet ds = new DataSet();
			
			ds.ReadXml(reader);
			int suffix = 1;
			Dictionary<string, string> dupList = new Dictionary<string, string>();
			
			if (ds.Tables.Count > 0)
			{
				DataTable dt = default(DataTable);
				DataColumn dc = default(DataColumn);
				dt = ds.Tables[0];
				if (dt.Rows.Count > 0)
				{
					foreach (DataColumn tempLoopVar_dc in dt.Columns)
					{
						dc = tempLoopVar_dc;
						if (dc.ColumnName == "Submitted_By")
						{
							string strSubmittedBy = Localization.GetString("SubmittedBy", this.SharedResouceFile);
							if (strSubmittedBy == string.Empty)
							{
								strSubmittedBy = "Submitted_By";
							}
							strSubmittedBy = Utility.GetValidControlID(strSubmittedBy);
							dc.ColumnName = strSubmittedBy;
						}
						if (dc.ColumnName == "Submit_Date")
						{
							string strSubmitDate = Localization.GetString("Submit Date", this.SharedResouceFile);
							if (string.IsNullOrEmpty(strSubmitDate))
							{
								strSubmitDate = "Submit_Date";
							}
							strSubmitDate = Utility.GetValidControlID(strSubmitDate);
							dc.ColumnName = strSubmitDate;
						}
						try
						{
							dc.ColumnName = (string) (dc.ColumnName.Replace("_", " ").Trim());
						}
						catch (DuplicateNameException)
						{
							dupList.Add(dc.ColumnName.Replace("_", " ").Trim(), dc.ColumnName.Replace("_", " ").Trim() + suffix.ToString());
							dc.ColumnName = (string) (dc.ColumnName.Replace("_", " ").Trim() + suffix.ToString());
							suffix++;
						}
						catch (Exception ex)
						{
							throw (ex);
						}
					}
				}
				else
				{
					bHaveData = false;
				}
			}
			else
			{
				bHaveData = false;
			}
			
			foreach (string key in dupList.Keys)
			{
				foreach (DataRow row in ds.Tables[0].Rows)
				{
					if (row[key] == System.DBNull.Value)
					{
						row[key] = row[dupList[key]];
					}
				}
				ds.Tables[0].Columns.Remove(dupList[key]);
			}
			
			//if (!bHaveData)
			//{
			//	throw (new ArgumentException("NoDataAvailable"));
			//}
			
			if (bHaveData)
			{
				//Add the recordCount Table to the DataSet
				DataTable oDataTable = new DataTable("Records");
				DataColumn oDataColumn = new DataColumn("RecordCount");
				oDataTable.Columns.Add(oDataColumn);

				DataRow oDataRow = oDataTable.NewRow();
				oDataRow["RecordCount"] = _intReportRecCount;
				oDataTable.Rows.Add(oDataRow);
				ds.Tables.Add(oDataTable);
			}
			
			return ds;
			
		}
		
		public DataSet List(int moduleId, DateTime dtStart, DateTime dtEnd, int pageNum, int pageSize, string modulePath, bool fromControl = true)
		{
			FormController objFormCtl = new FormController(moduleId);
			if (objFormCtl.FormInfo.UseExtTable)
			{
				return GetFormResultsExt(objFormCtl.FormInfo, moduleId, dtStart, dtEnd, pageNum, pageSize, fromControl);
			}
			else
			{
				return GetDataSetFromFormResults(GetFormResults(moduleId, dtStart, dtEnd, pageNum, pageSize, fromControl), modulePath);
			}
		}
		
		public int Add(FormDataInfo objFormData, SqlTransaction objTransaction = null)
		{
			return System.Convert.ToInt32(Code5Systems.FormMaster.Data.DataProvider.Instance().AddFormData(objFormData.FormID, objFormData.PortalID, objFormData.ModuleID, objFormData.FormData, objFormData.ExtTableRowId, objFormData.CreatedByID, objTransaction));
		}
		
		public void Update(FormDataInfo objFormData, SqlTransaction objTransaction = null)
		{
			Code5Systems.FormMaster.Data.DataProvider.Instance().UpdateFormData(objFormData.FormDataID, objFormData.FormID, objFormData.PortalID, objFormData.ModuleID, objFormData.FormData, objFormData.ExtTableRowId, objFormData.LastModifiedByID, objTransaction);
		}
		
		private int AddPageToExternalTable(FormController objFormCtl, FormDataInfo oFormDataInfo, FormPageInfo oPage, int foreignKeyID, List<FormMappingInfo> alMappings = null)
		{
			
			FormMappingController objMapCtl = new FormMappingController();
			XDocument xFormData = default(XDocument);
			XDocument xFormPageData = null;
			System.Collections.Generic.List<XElement> xAnswers = default(System.Collections.Generic.List<XElement>);
			StringBuilder sbQuery = new StringBuilder();
			FormMappingInfo oMapping = default(FormMappingInfo);
			bool hasForeignKeyField = false;
			FormMappingInfo foreignKeyMapping = new FormMappingInfo();
			XElement xPageData = default(XElement);
			xFormData = XDocument.Parse(oFormDataInfo.FormData);
			
			if (!objFormCtl.FormInfo.ExtTableForEachPage && objFormCtl.Pages.Count > 1)
			{
				//Combine all pages
				xPageData = xFormData.Root.Elements("Pages").Elements("Page").Where(p => System.Convert.ToInt32(p.Attributes("PageNum").First().Value) == 1).SingleOrDefault();
				if (xPageData != null)
				{
					xFormPageData = XDocument.Parse(xPageData.ToString());
					for (int i = 2; i <= objFormCtl.Pages.Count; i++)
					{
						int index = i;
						xPageData = xFormData.Root.Elements("Pages").Elements("Page").Where(p => System.Convert.ToInt32(p.Attributes("PageNum").First().Value) == index).SingleOrDefault();
						if (xPageData != null)
						{
							foreach (XElement field in xPageData.Elements("Field"))
							{
								xFormPageData.Root.Add(field);
							}
						}
					}
				}
				else
				{
					throw (new Exception("xFormPageData is Null DBMethods.vb"));
				}
			}
			else
			{
				xPageData = xFormData.Root.Elements("Pages").Elements("Page").Where(p => System.Convert.ToInt32(p.Attributes("PageNum").First().Value) == oPage.PageNum).SingleOrDefault();
				if (xPageData != null)
				{
					xFormPageData = XDocument.Parse(xPageData.ToString());
				}
				else
				{
					throw (new Exception("xFormPageData is Null DBMethods.vb"));
				}
			}
			
			if (alMappings == null)
			{
				alMappings = objMapCtl.GetByPage(oFormDataInfo.FormID, oPage.PageNum);
			}
			
			sbQuery.Append("INSERT INTO [" + oPage.ExtTableName + "] (" + Environment.NewLine);
			
			if (foreignKeyID > -1 && oPage.PageNum > 1)
			{
				string foreignKeyFieldName = (string) (objFormCtl.Pages[0].ExtIdentityColumn);
				DataSet dsTableInfo = objFormCtl.ExecuteDataSet(oPage.ExtTableConnStr, "EXEC sp_help [" + oPage.ExtTableName + "]");
				DataTable fieldTable = dsTableInfo.Tables[1];
				fieldTable.DefaultView.Sort = "Column_name";
				DataRowView[] keyFieldRow = fieldTable.DefaultView.FindRows(objFormCtl.Pages[0].ExtIdentityColumn);
				if (keyFieldRow.Count() == 1)
				{
					hasForeignKeyField = true;
					foreignKeyMapping.DataType = "int";
					foreignKeyMapping.PageNum = oPage.PageNum;
					foreignKeyMapping.TableField = (string) (objFormCtl.Pages[0].ExtIdentityColumn);
					foreignKeyMapping.FormField = Utility.GetValidControlID((string) (objFormCtl.Pages[0].ExtIdentityColumn));
					foreignKeyMapping.Value = (foreignKeyID).ToString();
					
					sbQuery.Append("[" + objFormCtl.Pages[0].ExtIdentityColumn + "]");
					if (alMappings.Count > 0)
					{
						sbQuery.Append("," + Environment.NewLine);
					}
					else
					{
						sbQuery.Append(Environment.NewLine);
					}
				}
			}
			
			for (int index = 0; index <= alMappings.Count - 1; index++)
			{
				oMapping = alMappings[index];
				sbQuery.Append("[" + oMapping.TableField + "]");
				if (index < alMappings.Count - 1)
				{
					sbQuery.Append("," + Environment.NewLine);
				}
				else
				{
					sbQuery.Append(Environment.NewLine);
				}
			}
			sbQuery.Append(") VALUES (" + Environment.NewLine);
			
			if (hasForeignKeyField)
			{
				sbQuery.Append("@" + Utility.GetValidControlID((string) (objFormCtl.Pages[0].ExtIdentityColumn)));
				if (alMappings.Count > 0)
				{
					sbQuery.Append("," + Environment.NewLine);
				}
				else
				{
					sbQuery.Append(Environment.NewLine);
				}
			}
			
			for (int index = 0; index <= alMappings.Count - 1; index++)
			{
				oMapping = alMappings[index];
				oMapping.Value = string.Empty;
				sbQuery.Append("@" + oMapping.FormField);
				if (index < alMappings.Count - 1)
				{
					sbQuery.Append("," + Environment.NewLine);
				}
				else
				{
					sbQuery.Append(Environment.NewLine);
				}
				
				xAnswers = (from a in xFormPageData.Root.Elements("Field").Elements("Answers").Elements("Answer") 
						   where a.Parent.Parent.Attributes("ID").First().Value == oMapping.FormField 
						   select a).ToList();
				
				//If we are importing legacy data the FormField ID will not be in the Data
				//so try to get the data by using the Field Label.
				if (xAnswers.Count() == 0 && (!string.IsNullOrEmpty(oMapping.FormFieldLabel)))
				{
					xAnswers = (from a in xFormPageData.Root.Elements("Field").Elements("Answers").Elements("Answer") 
								where a.Parent.Parent.Attributes("ID").First().Value == oMapping.FormField 
								select a).ToList();

					if (xAnswers.Count() == 0)
					{
						xAnswers = (from a in xFormPageData.Root.Elements("Field").Elements("Answers").Elements("Answer") 
								   where a.Parent.Parent.Element("Question").Value == Utility.GetValidXmlSafeString(oMapping.FormFieldLabel) 
								   select a).ToList();

						if (xAnswers.Count() == 0)
						{
							throw (new Exception(string.Format(Localization.GetString("ImportFormFileNotFound", Utility.GetSharedResourceFile()), oMapping.FormFieldLabel)));
						}
					}
				}
				
				for (int iNodeIndex = 0; iNodeIndex <= xAnswers.Count() - 1; iNodeIndex++)
				{
					switch (oMapping.DataType)
					{
						case "bit":
							string[] ansParts = xAnswers[iNodeIndex].Value.Split(new char[1] { ':' }, StringSplitOptions.RemoveEmptyEntries);
							if (ansParts[0] == Localization.GetString("Yes.Text", Utility.GetSharedResourceFile()))
							{
								oMapping.Value = "True";
							}
							else
							{
								oMapping.Value = "False";
							}
							break;
						default:
							oMapping.Value += (string) (xAnswers[iNodeIndex].Value);
							break;
					}
					
					if (iNodeIndex < xAnswers.Count - 1)
					{
						oMapping.Value += ",";
					}
				}
			}
			
			if (hasForeignKeyField)
			{
				alMappings.Insert(0, foreignKeyMapping);
			}
			
			sbQuery.Append(")" + Environment.NewLine);
			
			sbQuery.Append("SELECT SCOPE_IDENTITY()");
			
			return Code5Systems.FormMaster.Data.DataProvider.Instance().AddExtTableData(sbQuery.ToString(), alMappings, oPage.ExtTableConnStr, Utility.GetUserCulture());
			
		}
		
		public int AddToExternalTable(FormController objFormCtl, FormDataInfo oFormDataInfo, List<FormMappingInfo> alMappings = null)
		{
			
			//Dim xFormData As XDocument
			//Dim xAnswers As IEnumerable(Of XElement)
			//Dim oMapping As FormMappingInfo
			//Dim sbQuery As New StringBuilder()
			//Dim objMapCtl As New FormMappingController
			int firstForeignKeyID = -1;
			int foreignKeyID = -1;
			
			try
			{
				if (objFormCtl.FormInfo.ExtTableForEachPage)
				{
					foreach (FormPageInfo oPage in objFormCtl.Pages)
					{
						foreignKeyID = AddPageToExternalTable(objFormCtl, oFormDataInfo, oPage, firstForeignKeyID);
						if (firstForeignKeyID == -1)
						{
							firstForeignKeyID = foreignKeyID;
						}
					}
				}
				else
				{
					firstForeignKeyID = AddPageToExternalTable(objFormCtl, oFormDataInfo, objFormCtl.Pages[0], foreignKeyID);
				}
				return firstForeignKeyID;
			}
			catch (Exception ex)
			{
				string msg = default(string);
				if (!string.IsNullOrEmpty(Localization.GetString("SQLCustomError", Utility.GetSharedResourceFile())))
				{
					msg = Localization.GetString("SQLCustomError", Utility.GetSharedResourceFile());
				}
				else
				{
					msg = ex.Message;
				}
				throw (new ExtTableException(msg, ex, objFormCtl.FormInfo.ExtTableName, oFormDataInfo.FormDataID));
			}
			
		}
		
		public void UpdateExternalTable(FormController objFormCtl, FormDataInfo oformDataInfo)
		{
			XDocument xFormData = default(XDocument);
			System.Collections.Generic.List<XElement> xAnswers = default(System.Collections.Generic.List<XElement>);
			FormMappingInfo oMapping = default(FormMappingInfo);
			StringBuilder sbQuery = new StringBuilder();
			FormMappingController objMapCtl = new FormMappingController();
			List<FormMappingInfo> alMappings = default(List<FormMappingInfo>);
			System.Collections.IEnumerator myEnumerator;
			
			xFormData = XDocument.Parse(oformDataInfo.FormData);
			
			for (int i = 0; i <= objFormCtl.Pages.Count - 1; i++)
			{
				sbQuery.Remove(0, sbQuery.Length);
				alMappings = objMapCtl.GetByPage(oformDataInfo.FormID, System.Convert.ToInt32(objFormCtl.Pages[i].PageNum));
				myEnumerator = alMappings.GetEnumerator();
				
				sbQuery.Append("UPDATE [" + objFormCtl.Pages[i].ExtTableName + "] SET " + Environment.NewLine);
				for (int index = 0; index <= alMappings.Count - 1; index++)
				{
					oMapping = alMappings[index];
					oMapping.Value = string.Empty;
					
					sbQuery.Append("[" + oMapping.TableField + "] = ");
					sbQuery.Append("@" + oMapping.FormField);
					if (index < alMappings.Count - 1)
					{
						sbQuery.Append("," + Environment.NewLine);
					}
					else
					{
						sbQuery.Append(Environment.NewLine);
					}
					
					xAnswers = xFormData.Descendants("Answer").Where(a => a.Parent.Parent.Attributes("ID").First().Value == oMapping.FormField).ToList();
					
					for (int iNodeIndex = 0; iNodeIndex <= xAnswers.Count() - 1; iNodeIndex++)
					{
						switch (oMapping.DataType)
						{
							case "bit":
								if (xAnswers[iNodeIndex].Value == Localization.GetString("Yes.Text", Utility.GetSharedResourceFile()))
								{
									oMapping.Value = "True";
								}
								else
								{
									oMapping.Value = "False";
								}
								break;
								
							default:
								oMapping.Value += (string) (xAnswers[iNodeIndex].Value);
								break;
								
						}
						if (iNodeIndex < xAnswers.Count - 1)
						{
							oMapping.Value += ",";
						}
					}
					
				}
				
				sbQuery.Append("WHERE " + objFormCtl.Pages[0].ExtIdentityColumn + " = " + oformDataInfo.ExtTableRowId.ToString());
				
				if (alMappings.Count> 0)
				{
					Code5Systems.FormMaster.Data.DataProvider.Instance().UpdateExternalTable(sbQuery.ToString(), alMappings, objFormCtl.FormInfo.ExtTableConnStr, Utility.GetUserCulture());
				}
				
				if (!objFormCtl.FormInfo.ExtTableForEachPage)
				{
					break;
				}
			}
		}
		
		public DataTransferInfo CopyDataFromXmlStore(FormController objFormCtl, string modulePath, List<FormMappingInfo> alMappings)
		{
			DataTransferInfo oDTInfo = new DataTransferInfo();
			List<FormDataInfo> listFormData = default(List<FormDataInfo>);
			
			oDTInfo.ExtTableName = objFormCtl.FormInfo.CurrentPage.ExtTableName;
			
			listFormData = GetFormDataToCopy(objFormCtl.FormInfo.ModuleID, GetExternalTableIDCsvString(objFormCtl.FormInfo));
			
			foreach (FormDataInfo item in listFormData)
			{
				try
				{
					oDTInfo.TotalRows++;
					this.VerifyExtTableSchemaToXmlData(objFormCtl, item, alMappings);
					if (objFormCtl.FormInfo.CurrentPage.PageNum == 1)
					{
						//item.ExtTableRowId = Me.AddToExternalTable(objFormCtl, item, alMappings)
						//Me.Update(item)
					}
					oDTInfo.SuccessRows++;
				}
				catch (ExtTableException ex)
				{
					oDTInfo.FailedRows++;
					oDTInfo.ExceptionList.Add(ex);
				}
			}
			
			return oDTInfo;
			
		}
		
		private void VerifyExtTableSchemaToXmlData(FormController objFormCtl, FormDataInfo oFormDataInfo, List<FormMappingInfo> alMappings)
		{
			DataSet dsTableInfo = default(DataSet);
			XDocument xDoc = XDocument.Parse(oFormDataInfo.FormData);
			XElement xQuestion = default(XElement);
			
			dsTableInfo = objFormCtl.ExecuteDataSet(objFormCtl.FormInfo.CurrentPage.ExtTableConnStr, "EXEC sp_help [" + objFormCtl.FormInfo.CurrentPage.ExtTableName + "]");
			DataTable colTable = dsTableInfo.Tables[1];
			DataView dView = colTable.DefaultView;
			foreach (FormMappingInfo mapping in alMappings)
			{
				FormMappingInfo localMapping = mapping;
				dView.RowFilter = string.Format("Column_Name = \'{0}\'", mapping.TableField);
				
				if (dView.Count == 0)
				{
					throw (new ExtTableException(string.Format(Localization.GetString("InvalidMapping", Utility.GetSharedResourceFile()), localMapping.TableField), null, objFormCtl.FormInfo.CurrentPage.ExtTableName, oFormDataInfo.FormDataID));
				}
				
				if ((dView[0].Row["Type"]).ToString() != localMapping.DataType)
				{
					throw (new ExtTableException(string.Format(Localization.GetString("InvalidDataType", Utility.GetSharedResourceFile()), localMapping.DataType, localMapping.FormFieldLabel), null, objFormCtl.FormInfo.CurrentPage.ExtTableName, oFormDataInfo.FormDataID));
				}
				
				xQuestion = (from c in xDoc.Root.Elements("Pages").Elements("Field") where c.Attributes("ID").First().Value == localMapping.FormField select c).FirstOrDefault();
				
				if (xQuestion == null)
				{
					throw (new ExtTableException(string.Format(Localization.GetString("MissingFormField", Utility.GetSharedResourceFile()), localMapping.TableField, localMapping.FormFieldLabel), null, objFormCtl.FormInfo.CurrentPage.ExtTableName, oFormDataInfo.FormDataID));
				}
				
			}
			
		}
		
		public DataTransferInfo AttachExtTableData(FormInfo oFormInfo, int userId)
		{
			DataTransferInfo oDTInfo = new DataTransferInfo();
			FormDataInfo oFormDataInfo = default(FormDataInfo);
			FormDataController oFormDataCtlr = new FormDataController();
			string sql = string.Format("SELECT {0} FROM {1}{2}", oFormInfo.ExtIdentityColumn, Code5Systems.FormMaster.Data.DataProvider.Instance().ObjectQualifier, oFormInfo.ExtTableName);
			oDTInfo.ExtTableName = oFormInfo.ExtTableName;
			string connStr = string.Empty;
			
			if (!string.IsNullOrEmpty(oFormInfo.ExtTableConnStr))
			{
				connStr = oFormInfo.ExtTableConnStr;
			}
			
			DataTable dt = Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteSQL(connStr, sql);
			foreach (DataRow row in dt.Rows)
			{
				oDTInfo.TotalRows++;
				oFormDataInfo = new FormDataInfo();
				oFormDataInfo.FormID = oFormInfo.FormID;
				oFormDataInfo.ModuleID = oFormInfo.ModuleID;
				oFormDataInfo.PortalID = oFormInfo.PortalID;
				oFormDataInfo.ExtTableRowId = int.Parse((string) (row[oFormInfo.ExtIdentityColumn].ToString()));
				oFormDataInfo.CreatedByID = userId;
				oFormDataInfo.FormData = "<FormResults/>";
				try
				{
					oFormDataCtlr.Add(oFormDataInfo);
					oDTInfo.SuccessRows++;
				}
				catch (Exception ex)
				{
					oDTInfo.FailedRows++;
					oDTInfo.ExceptionList.Add((ExtTableException) ex);
				}
			}
			
			return oDTInfo;
			
		}
		
		public string GetExternalTableIDCsvString(FormInfo oFormInfo)
		{
			StringBuilder sbIdCSV = new StringBuilder();
			
			string strQuery = "SELECT [" + oFormInfo.CurrentPage.ExtIdentityColumn + "] FROM [" + oFormInfo.CurrentPage.ExtTableName + "]" + Environment.NewLine;
			
			using (IDataReader dr = Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteDataReader(oFormInfo.ExtTableConnStr, strQuery))
			{
				while (dr.Read())
				{
					sbIdCSV.Append((dr.GetValue(0)).ToString() + ",");
				}
			}
			
			
			if (sbIdCSV.Length > 0)
			{
				sbIdCSV.Remove(sbIdCSV.Length - 1, 1);
			}
			else
			{
				sbIdCSV.Append(int.MinValue.ToString());
			}
			
			return sbIdCSV.ToString();
			
		}
		
		public string GetExtIdByDate(int moduleId, DateTime dtStart, DateTime dtEnd)
		{
			string strIdCsv = string.Empty;
			
			using (IDataReader dr = Code5Systems.FormMaster.Data.DataProvider.Instance().GetExtIdByDate(moduleId, dtStart, dtEnd))
			{
				while (dr.Read())
				{
					strIdCsv += (string) ((dr.GetValue(0)).ToString() + ",");
				}
			}
			
			
			if (strIdCsv.Length > 0)
			{
				strIdCsv = strIdCsv.Remove(strIdCsv.Length - 1, 1);
			}
			
			return strIdCsv;
			
		}
		
		public void DeleteExtTableRange(FormInfo oFormInfo, int moduleId, DateTime dtStart, DateTime dtEnd)
		{
			string sQuery = default(string);
			FormController objFormCtlr = new FormController(moduleId);
			string sIdCsv = GetExtIdByDate(moduleId, dtStart, dtEnd);
			
			if (!string.IsNullOrEmpty(sIdCsv))
			{
				for (int i = objFormCtlr.Pages.Count - 1; i >= 0; i--)
				{
					sQuery = string.Empty;
					sQuery = "DELETE FROM [" + objFormCtlr.Pages[i].ExtTableName + "]" + Environment.NewLine;
					sQuery += "WHERE " + objFormCtlr.Pages[0].ExtIdentityColumn + " IN (" + sIdCsv + ")";
					Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteSQL(oFormInfo.ExtTableConnStr, sQuery);
				}
			}
		}
		
		public void DeleteRange(FormInfo oFormInfo, int moduleId, DateTime dtStart, DateTime dtEnd, string modulePath)
		{
			SqlTransaction trans = default(SqlTransaction);
			SqlConnection conn = new SqlConnection();
			
			// Start a Transaction in case any part of the update fails
			// We are updating multiple tables and deleting disk files
			conn = new SqlConnection(Code5Systems.FormMaster.Data.DataProvider.Instance().ConnectionString);
			conn.Open();
			trans = conn.BeginTransaction();
			
			try
			{
				int intFormDataId = default(int);
				FormFileDataController objFileCtl = new FormFileDataController();
				List<FormFileDataInfo> aryFileDataInfo = new List<FormFileDataInfo>();
				
				//Get a Dataset of the records to be deleted
				DataSet ds = List(moduleId, dtStart, dtEnd, 1, int.MaxValue, modulePath);
				DataTable oTable = ds.Tables[0];
				
				//Need to delete any Uploaded Files associated with the records to be deleted
				//and delete the Upload records in C5SLLC_FormFileData table or
				//delete the files from disk
				foreach (DataRow row in oTable.Rows)
				{
					intFormDataId = System.Convert.ToInt32(row["FormDataId"]);
					List<FormFileDataInfo> aryTemp = objFileCtl.GetByFormData(intFormDataId);
					
					foreach (FormFileDataInfo item in aryTemp)
					{
						aryFileDataInfo.Add(item);
					}
				}
				
				//Delete the File Upload Record
				foreach (FormFileDataInfo item in aryFileDataInfo)
				{
					objFileCtl.Delete(item.FileDataId, trans);
				}
				
				if (oFormInfo.UseExtTable)
				{
					DeleteExtTableRange(oFormInfo, moduleId, dtStart, dtEnd);
				}
				
				//Delete the Range of Records
				Code5Systems.FormMaster.Data.DataProvider.Instance().DeleteFormDataRange(moduleId, dtStart, dtEnd, trans);
				
				//Process the Database updates
				trans.Commit();
				
				//No Problems to this point so delete the File System files
				//This is of course permanent no Rollback here
				//Worse case is Orphaned files
				foreach (FormFileDataInfo item in aryFileDataInfo)
				{
					if (File.Exists(HttpContext.Current.Server.MapPath(item.FullFilePath)))
					{
						File.Delete(HttpContext.Current.Server.MapPath(item.FullFilePath));
					}
				}
			}
			catch (ArgumentException)
			{
				trans.Rollback();
			}
			catch (IOException ioexc)
			{
				trans.Rollback();
				throw (ioexc);
			}
			catch (SqlException dbexc)
			{
				trans.Rollback();
				throw (dbexc);
			}
			catch (Exception exc)
			{
				trans.Rollback();
				throw (exc);
			}
			finally
			{
				if (conn.State == ConnectionState.Open)
				{
					conn.Close();
				}
			}
		}
		
		public void Delete(int moduleId, int formDataId, FormInfo oFormInfo)
		{
			SqlTransaction trans = default(SqlTransaction);
			SqlConnection conn = new SqlConnection();
			FormDataInfo objFormDataInfo = null;
			
			// Start a Transaction in case any part of the update fails
			// We are updateing multiple tables and deleting disk files
			conn = new SqlConnection(Code5Systems.FormMaster.Data.DataProvider.Instance().ConnectionString);
			conn.Open();
			trans = conn.BeginTransaction();
			
			try
			{
				FormFileDataController objFileCtl = new FormFileDataController();
				List<FormFileDataInfo> aryFileDataInfo = default(List<FormFileDataInfo>);
				FormController objFormCtlr = default(FormController);
				string sQuery = default(string);
				
				//Need to delete any Uploaded Files associated with the records to be deleted
				//and delete the Upload records in C5SLLC_FormFileData table or
				//delete the files from disk
				
				//Get Any Files associated with this record
				aryFileDataInfo = objFileCtl.GetByFormData(formDataId);
				
				//Delete the File Upload Record
				foreach (FormFileDataInfo item in aryFileDataInfo)
				{
					objFileCtl.Delete(item.FileDataId, trans);
				}
				
				//If Using an External Table get the Information needed to delete it
				//before deleting the main Form Master Record
				if (oFormInfo.UseExtTable)
				{
					objFormDataInfo = this.Get(formDataId, moduleId);
				}
				
				Code5Systems.FormMaster.Data.DataProvider.Instance().DeleteFormData(moduleId, formDataId, trans);
				
				//Delete the External Table Data. If this fails the transaction will be aborted
				if (oFormInfo.UseExtTable)
				{
					objFormCtlr = new FormController(moduleId);
					for (int i = objFormCtlr.Pages.Count - 1; i >= 0; i--)
					{
						sQuery = string.Empty;
						sQuery = "DELETE FROM [" + objFormCtlr.Pages[i].ExtTableName + "]" + Environment.NewLine;
						sQuery += "WHERE " + objFormCtlr.Pages[0].ExtIdentityColumn + " = " + objFormDataInfo.ExtTableRowId.ToString();
						Code5Systems.FormMaster.Data.DataProvider.Instance().DeleteExternalTable(sQuery, oFormInfo.CurrentPage.ExtTableConnStr);
					}
				}
				
				//Since we are possible dealing with multiple records, we commit
				//before deleting the files, since file delete cannot be rolled back
				
				//Process the Database updates
				trans.Commit();
				
				//No Problems to this point so delete the File System files
				//This is of course permanent no Rollback here
				//Worse case is Orphaned files
				foreach (FormFileDataInfo item in aryFileDataInfo)
				{
					if (File.Exists(HttpContext.Current.Server.MapPath(item.FullFilePath)))
					{
						File.Delete(HttpContext.Current.Server.MapPath(item.FullFilePath));
					}
				}
				
			}
			catch (IOException)
			{
				trans.Rollback();
				throw;
			}
			catch (SqlException)
			{
				trans.Rollback();
				throw;
			}
			catch (Exception)
			{
				trans.Rollback();
				throw;
			}
			finally
			{
				if (conn.State == ConnectionState.Open)
				{
					conn.Close();
				}
			}
		}
		
		public void SetExtTableIdNull(int moduleId)
		{
			Code5Systems.FormMaster.Data.DataProvider.Instance().SetExtTableIdNull(moduleId);
		}
		
		public void SetPageExtTableIdNull(int formID, int pageNum)
		{
			Code5Systems.FormMaster.Data.DataProvider.Instance().SetPageExtTableIdNull(formID, pageNum);
		}
		
		public DataSet ExecuteDataSet(string connString, string query)
		{
			return Code5Systems.FormMaster.Data.DataProvider.Instance().ExecuteDataSet(connString, query);
		}
		
#endregion
		
	}
}
